/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Factory.DAO.MySQL;

import DatabaseConnection.DBConnectionFactory;
import Factory.DAO.CategoryDAO;
import Factory.DAOFactory;
import Factory.Tables.Category;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Patrick
 */
public class MySQLCategoryDAO extends CategoryDAO{

    @Override
    public void addCategory(Category obj) {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("insert into category (categorypic,categoryname,categorydescription) values (?,?,?) ");
            pstmt.setString(1, obj.getCategoryPic());
            pstmt.setString(2, obj.getCategoryName());
            pstmt.setString(3, obj.getCategoryDescription());
            pstmt.executeUpdate();
            conn.close();
        }
        catch(Exception ex){
            Logger.getLogger(MySQLCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    
    @Override
    public List<Category> getAllCategories() {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select * from category order by categoryname");
            ResultSet rs = pstmt.executeQuery();
            ArrayList<Category> result = new ArrayList<>();
            Category category;
            while(rs.next()){
                category = new Category();
                category.setCategoryID(rs.getInt("idcategory"));
                category.setCategoryPic(rs.getString("categorypic"));
                category.setCategoryName(rs.getString("categoryname"));
                category.setCategoryDescription(rs.getString("categorydescription"));
                
                result.add(category);
            }
            conn.close();
            return result;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public void UpdateCategory(Category obj) {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("update category SET categorypic=?, categoryname=?, categorydescription=? where idcategory=?");
            pstmt.setString(1, obj.getCategoryPic());
            pstmt.setString(2, obj.getCategoryName());
            pstmt.setString(3, obj.getCategoryDescription());
            pstmt.setInt(4, obj.getCategoryID());
            pstmt.executeUpdate();
            conn.close();
        }
        catch(Exception ex){
            Logger.getLogger(MySQLCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void removeCategory(int categoryID) {
        
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("delete from category where idcategory=?");
            pstmt.setInt(1, categoryID);
            pstmt.executeUpdate();
            conn.close();
        }
        catch(Exception ex){
            Logger.getLogger(MySQLCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void UpdateCategoryName(int categoryID, String name) {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("update category SET categoryname=? where idcategory=?");
            pstmt.setString(1, name);
            pstmt.setInt(2, categoryID);
            pstmt.executeUpdate();
            conn.close();
        }
        catch(Exception ex){
            Logger.getLogger(MySQLCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void UpdateCategoryDescription(int categoryID, String description) {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("update category SET description=? where idcategory=?");
            pstmt.setString(1, description);
            pstmt.setInt(2, categoryID);
            pstmt.executeUpdate();
            conn.close();
        }
        catch(Exception ex){
            Logger.getLogger(MySQLCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
    }

    @Override
    public String getCategoryName(int categoryID) {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select categoryname from category where idcategory=?");
            pstmt.setInt(1, categoryID);
            ResultSet rs = pstmt.executeQuery();
            String name = null;
            while(rs.next()){
                name = rs.getString("categoryname");
            }
            conn.close();
            return name;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public Category getCategoryById(int categoryID) {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select * from category where idcategory=?");
            pstmt.setInt(1, categoryID);
            ResultSet rs = pstmt.executeQuery();
            Category category = null;
            while(rs.next()){
                category = new Category();
                category.setCategoryID(rs.getInt("idcategory"));
                category.setCategoryPic(rs.getString("categorypic"));
                category.setCategoryName(rs.getString("categoryname"));
                category.setCategoryDescription(rs.getString("categorydescription"));
            }
            conn.close();
            return category;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    @Override
    public Category getCategoryByName(String categoryName) {
        try{
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("select * from category where categoryname=?");
            pstmt.setString(1, categoryName);
            ResultSet rs = pstmt.executeQuery();
            Category category = null;
            while(rs.next()){
                category = new Category();
                category.setCategoryID(rs.getInt("idcategory"));
                category.setCategoryPic(rs.getString("categorypic"));
                category.setCategoryName(rs.getString("categoryname"));
                category.setCategoryDescription(rs.getString("categorydescription"));
            }
            conn.close();
            return category;
        }
        catch(Exception ex){
            Logger.getLogger(MySQLCategoryDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

   
}
